import random

import pymysql
from pymysql.cursors import DictCursor
# Database configuration
DB_CONFIG = {
    'host': '154.12.55.218',  # Database server address
    'user': 'DeviceData',  # Database username
    'password': 'DeviceData',  # Database password
    'db': 'devicedata',  # Database name
    'charset': 'utf8mb4',  # Character encoding
}

def run_updates(message):
    # Establish a connection to the database
    connection = pymysql.connect(**DB_CONFIG)
    try:
        # Perform all update operations using the same connection
        update(connection, message)
        update_daily_summary(connection)
        update_hourly_summary(connection)
        update_device_overall_summary(connection)
    except Exception as e:
        print(f"Database error during updates: {e}")
        connection.rollback()
    finally:
        connection.close()

def update(connection, message):
    print(type(message))  # 应该输出 <class 'dict'>
    print(message)  # 输出实际的 message 内容，确保它是字典

    try:
        with connection.cursor() as cursor:
            # Check if a record with the same primary key already exists
            check_sql = "SELECT * FROM DeviceData WHERE devID = %s AND timestamp = %s"
            cursor.execute(check_sql, (message['devID'], message['timestamp']))
            if cursor.fetchone():
                print("Record already exists, skipping insert.")
                return

            # SQL insertion statement
            sql = """
                    INSERT INTO DeviceData (devID, flowS, flowL, heat, latitude, longitude, restrict_data, speed, overspeed_num, wrong_direction_num, rate_limiting, timestamp, record_time) 
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, FROM_UNIXTIME(%s / 1000))
                    """
            cursor.execute(sql, (message['devID'], message['flowS'], message['flowL'],
                                 message['heat'], message['latitude'], message['longitude'],
                                 message['restrict_data'], message['speed'], message['overspeed_num'],
                                 message['wrong_direction_num'],message['rate_limiting'],
                                 message['timestamp'], message['timestamp']))

            # Commit to the database
            connection.commit()
    except Exception as e:
        print(f"Database error in update: {e}")
        raise

def update_daily_summary(connection):
    try:
        with connection.cursor() as cursor:
            sql = """
                INSERT INTO DailyTrafficSummary (devID, date, totalFlow, avgLatitude, avgLongitude)
                SELECT 
                    devID, DATE(FROM_UNIXTIME(timestamp/1000)) AS date, SUM(flowS + flowL) AS totalFlow, 
                    AVG(latitude) AS avgLatitude, AVG(longitude) AS avgLongitude
                FROM DeviceData
                GROUP BY devID, DATE(FROM_UNIXTIME(timestamp/1000))
                ON DUPLICATE KEY UPDATE totalFlow=VALUES(totalFlow), avgLatitude=VALUES(avgLatitude), avgLongitude=VALUES(avgLongitude);
                """
            cursor.execute(sql)
            connection.commit()
    except Exception as e:
        print(f"Database error in update_daily_summary: {e}")
        raise
def update_hourly_summary(connection):
    try:
        with connection.cursor() as cursor:
            sql = """
             INSERT INTO HourlyTrafficSummary (devID, date, hour, small_vehicle_flow, large_vehicle_flow, total_vehicle_flow, avgLatitude, avgLongitude)
             SELECT 
                 devID,
                 DATE(FROM_UNIXTIME(timestamp/1000)) AS date,
                 HOUR(FROM_UNIXTIME(timestamp/1000)) AS hour,
                 SUM(flowS) AS small_vehicle_flow,
                 SUM(flowL) AS large_vehicle_flow,
                 SUM(flowS + flowL) AS total_vehicle_flow,
                 AVG(latitude) AS avgLatitude,
                 AVG(longitude) AS avgLongitude
             FROM 
                 DeviceData
             GROUP BY 
                 devID, DATE(FROM_UNIXTIME(timestamp/1000)), HOUR(FROM_UNIXTIME(timestamp/1000))
             ON DUPLICATE KEY UPDATE
                 small_vehicle_flow = VALUES(small_vehicle_flow),
                 large_vehicle_flow = VALUES(large_vehicle_flow),
                 total_vehicle_flow = VALUES(total_vehicle_flow),
                 avgLatitude = VALUES(avgLatitude),
                 avgLongitude = VALUES(avgLongitude);
             """
            cursor.execute(sql)
            connection.commit()
    except Exception as e:
        print(f"Database error in update_hourly_summary: {e}")
        raise
def update_device_overall_summary(connection):
    try:
        with connection.cursor() as cursor:
            # 更新总结表格
            cursor.execute("SET SESSION group_concat_max_len = 1048576;")  # 根据需要调整大小
            update_summary_sql = """
            INSERT INTO DeviceOverallSummary (devID, totalFlow, smallVehicleFlow, largeVehicleFlow, latestHeat, avgSpeed, latestRestrict, latestRate, overspeed_num_sum, wrong_direction_num_sum)  
            SELECT   
                dd.devID,  
                SUM(dd.flowS + dd.flowL) AS totalFlow,  
                SUM(dd.flowS) AS smallVehicleFlow,  
                SUM(dd.flowL) AS largeVehicleFlow,  
                SUBSTRING_INDEX(GROUP_CONCAT(dd.heat ORDER BY dd.record_time DESC), ',', 1) AS latestHeat,  
                AVG(dd.speed) AS avgSpeed,  
                SUBSTRING_INDEX(GROUP_CONCAT(dd.restrict_data ORDER BY dd.record_time DESC), ',', 1) AS latestRestrict,  
                SUBSTRING_INDEX(GROUP_CONCAT(dd.rate_limiting ORDER BY dd.record_time DESC), ',', 1) AS latestRate,  
                SUM(dd.overspeed_num) AS overspeed_num_sum,  
                SUM(dd.wrong_direction_num) AS wrong_direction_num_sum
            FROM   
                DeviceData dd  
            GROUP BY   
                dd.devID  
            ON DUPLICATE KEY UPDATE  
                totalFlow = VALUES(totalFlow),  
                smallVehicleFlow = VALUES(smallVehicleFlow),  
                largeVehicleFlow = VALUES(largeVehicleFlow),  
                latestHeat = VALUES(latestHeat),  
                avgSpeed = VALUES(avgSpeed),  
                latestRestrict = VALUES(latestRestrict),  
                latestRate = VALUES(latestRate),  
                overspeed_num_sum = VALUES(overspeed_num_sum),  
                wrong_direction_num_sum = VALUES(wrong_direction_num_sum);
            """
                #             latestLatitude = VALUES(latestLatitude),
                # latestLongitude = VALUES(latestLongitude),
                #             SUBSTRING_INDEX(GROUP_CONCAT(dd.latitude ORDER BY dd.record_time DESC), ',', 1) AS latestLatitude,
                # SUBSTRING_INDEX(GROUP_CONCAT(dd.longitude ORDER BY dd.record_time DESC), ',', 1) AS latestLongitude,
            cursor.execute(update_summary_sql)
            connection.commit()
    except Exception as e:
        print(f"数据库更新错误 in update_device_overall_summary: {e}")
        raise


# 示例用法
if __name__ == '__main__':

    # for i in range(2):
    #     test_message = {
    #         'devID': 1528564110,
    #         'flowS': random.choice([1,2,3,4,5,16,17,18]),
    #         'flowL': random.choice([1,2,3,4,5,6,7,8,5,16,17,18]),
    #         'heat': 246+random.choice([1,2,3,4,5,6]),
    #         'latitude': 31.320488,
    #         'longitude': 121.395337,
    #         'speed': 30+random.choice([1,2,3,4,5,6]),
    #         'measure_height': 626+random.choice([1,2,3,4,5,6]),
    #         'timestamp': 1702450039387+i*1200001
    #     }
    #     run_updates(test_message)
    # for i in range(300):
    #     test_message = {
    #         'devID': 1528564111,
    #         'flowS': random.choice([1,2,3,4,5,16,17,18]),
    #         'flowL': random.choice([1,2,3,4,5,6,7,8,5,16,17,18]),
    #         'heat': 246+random.choice([1,2,3,4,5,6]),
    #         'latitude': 31.320635,
    #         'longitude': 121.394071,
    #         'speed': 30+random.choice([1,2,3,4,5,6]),
    #         'measure_height': 626+random.choice([1,2,3,4,5,6]),
    #         'timestamp': 1702450039387+i*1200001
    #     }
    #     run_updates(test_message)
        test_message={'devID': 1528564111, 'flowS': 10, 'flowL': 15, 'heat': 210, 'latitude': 31.318803, 'longitude':121.395408, 'restrict_data': 4000, 'speed': 11.956161, 'overspeed_num': 0,  'wrong_direction_num': 0,  'rate_limiting': 30, 'timestamp': '1714452798702'}
    #
    # for i in range(300):
    #     test_message = {
    #         'devID': 1528564112,
    #         'flowS': random.choice([1,2,3,4,5,16,17,50]),
    #         'flowL': random.choice([6,7,8,5,16,17,18]),
    #         'heat': 246+random.choice([1,2,3,4,5,6]),
    #         'latitude': 31.320307,
    #         'longitude': 121.397767,
    #         'speed': 30+random.choice([1,2,3,4,5,6]),
    #         'measure_height': 626+random.choice([1,2,3,4,5,6]),
    #         'timestamp': 1702526839451+i*1200001
    #     }
        run_updates(test_message)
